const express = require('express');
const mysql = require('mysql2');
const cors = require('cors');
const multer = require('multer');
const fs = require('fs');
const { parse } = require('csv-parse/sync');

const app = express();
app.use(cors());
app.use(express.json());

// 增加请求大小限制
app.use(express.json({ limit: '100mb' }));
app.use(express.urlencoded({ limit: '100mb', extended: true }));

// 配置文件上传
const storage = multer.diskStorage({
    destination: function (req, file, cb) {
        // 确保上传目录存在
        if (!fs.existsSync('uploads/')) {
            fs.mkdirSync('uploads/');
        }
        cb(null, 'uploads/');
    },
    filename: function (req, file, cb) {
        cb(null, Date.now() + '-' + file.originalname);
    }
});

const upload = multer({
    storage: storage,
    limits: {
        fileSize: 100 * 1024 * 1024, // 100MB in bytes
    },
    fileFilter: function (req, file, cb) {
        // 检查文件类型
        if (!file.originalname.toLowerCase().endsWith('.csv')) {
            return cb(new Error('Only CSV files are allowed!'));
        }
        cb(null, true);
    }
});

// 创建数据库连接
const connection = mysql.createConnection({
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: '123456',
    database: 'user_db',
    // 增加最大允许数据包大小
    maxAllowedPacket: 1024 * 1024 * 64, // 64MB
});

// 连接数据库
connection.connect((err) => {
    if (err) {
        console.error('数据库连接失败:', err);
        return;
    }
    console.log('数据库连接成功');

    // 创建用户表
    const createUserTableSQL = `
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(255) NOT NULL UNIQUE,
            password VARCHAR(255) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    `;

    connection.query(createUserTableSQL, (err) => {
        if (err) {
            console.error('创建用户表失败:', err);
            return;
        }
        console.log('用户表创建成功或已存在');
    });
});

// 文件上传和数据处理接口
app.post('/api/upload', upload.single('file'), async (req, res) => {
    console.log('Received upload request');

    if (!req.file) {
        console.log('No file received');
        return res.status(400).json({ success: false, message: 'No file uploaded' });
    }

    console.log('File received:', req.file);
    let tableName = '';

    try {
        // 读取文件内容
        const fileContent = fs.readFileSync(req.file.path);
        // 解析 CSV 数据
        const records = parse(fileContent, {
            trim: true,
            skip_empty_lines: true,
        });

        // 获取列名（第一行）
        const headers = records[0];
        // 获取数据行（除去第一行）
        const dataRows = records.slice(1);

        const rowCount = dataRows.length;
        const columnCount = headers.length;

        console.log('CSV parsed successfully, row count:', rowCount);
        console.log('Column count:', columnCount);

        // 使用文件名作为表名（移除.csv后缀）
        tableName = req.file.originalname.toLowerCase().replace('.csv', '').replace(/[^a-z0-9_]/g, '_');
        console.log('Creating table:', tableName);

        // 构建创建表的SQL语句
        let createTableSQL = `CREATE TABLE IF NOT EXISTS ${tableName} (
            id INT AUTO_INCREMENT PRIMARY KEY,
            timestamp INT NOT NULL`;

        // 添加动态列（跳过第一列，因为它是时间步）
        for (let i = 1; i < columnCount; i++) {
            createTableSQL += `,\`${headers[i]}\` FLOAT COMMENT '${headers[i]}'`;
        }
        createTableSQL += ')';

        // 创建表
        await connection.promise().query(createTableSQL);
        console.log('Table created successfully');

        // 分批插入数据，使用更小的批次
        const batchSize = 50;
        for (let i = 0; i < dataRows.length; i += batchSize) {
            const batch = dataRows.slice(i, i + batchSize);
            // 第一列是时间步，其余列是数据
            const values = batch.map(row => {
                const timestamp = parseInt(row[0]);
                const flowValues = row.slice(1).map(Number);
                return [timestamp, ...flowValues];
            });

            try {
                const insertSQL = `INSERT INTO ${tableName} (timestamp, ${headers.slice(1).map(h => `\`${h}\``).join(', ')}) VALUES ?`;
                await connection.promise().query(insertSQL, [values]);
                console.log(`Inserted batch ${Math.floor(i / batchSize) + 1} of ${Math.ceil(dataRows.length / batchSize)}`);
            } catch (insertError) {
                console.error('Error inserting batch:', insertError);
                // 如果插入失败，删除表并抛出错误
                await connection.promise().query(`DROP TABLE IF EXISTS ${tableName}`);
                throw insertError;
            }
        }

        console.log('All data inserted successfully');

        // 删除临时文件
        fs.unlink(req.file.path, (err) => {
            if (err) {
                console.error('Error deleting temp file:', err);
            }
        });

        res.json({
            success: true,
            message: 'Data saved successfully',
            tableName: tableName,
            rowCount: rowCount,
            columnCount: columnCount - 1, // 减去时间步列
            headers: headers.slice(1) // 返回除时间步外的列名
        });
    } catch (error) {
        console.error('Error processing file:', error);

        // 如果表已创建但处理失败，清理表
        if (tableName) {
            try {
                await connection.promise().query(`DROP TABLE IF EXISTS ${tableName}`);
                console.log('Cleaned up incomplete table');
            } catch (cleanupError) {
                console.error('Error cleaning up table:', cleanupError);
            }
        }

        // 删除临时文件
        if (req.file && req.file.path) {
            fs.unlink(req.file.path, () => { });
        }

        res.status(500).json({
            success: false,
            message: '文件处理错误: ' + error.message,
            details: error.code === 'ER_NET_PACKET_TOO_LARGE' ? '数据量过大，请尝试减小文件大小或联系管理员调整服务器配置' : undefined
        });
    }
});

// 注册接口
app.post('/api/register', (req, res) => {
    const { username, password } = req.body;

    // 检查用户是否已存在
    connection.query(
        'SELECT * FROM users WHERE username = ?',
        [username],
        (err, results) => {
            if (err) {
                res.status(500).json({ error: '服务器错误' });
                return;
            }

            if (results.length > 0) {
                res.status(400).json({ error: '用户名已存在' });
                return;
            }

            // 插入新用户
            connection.query(
                'INSERT INTO users (username, password) VALUES (?, ?)',
                [username, password],
                (err) => {
                    if (err) {
                        res.status(500).json({ error: '注册失败' });
                        return;
                    }
                    res.json({ message: '注册成功' });
                }
            );
        }
    );
});

// 登录接口
app.post('/api/login', (req, res) => {
    const { username, password } = req.body;

    connection.query(
        'SELECT * FROM users WHERE username = ? AND password = ?',
        [username, password],
        (err, results) => {
            if (err) {
                res.status(500).json({ error: '服务器错误' });
                return;
            }

            if (results.length === 0) {
                res.status(401).json({ error: '用户名或密码错误' });
                return;
            }

            res.json({ message: '登录成功' });
        }
    );
});

const PORT = 3000;
app.listen(PORT, () => {
    console.log(`服务器运行在 http://localhost:${PORT}`);
}); 